﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Dynamic;
using System.IO;
using System.Text.RegularExpressions;
using System.Transactions;
using System.Web.Script.Services;
using System.Web.Services;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Dimok;
using Dimok.Data;
using Dimok.Images;
using Dimok.Web.UI;
using Data.LINQ;
using Dimok.Exceptions;
using TelerikExtensions;

namespace WSA {
	[WebService(Namespace = "http://motobankcorp.com/uploads")]
	[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
	[ScriptService]
	public class Common : System.Web.Services.WebService {
		#region GetList commons
		private static string BuildFilter(Type EntityType, string FilterLinq, string FilterTemp) {
			List<string> lstFilter = new List<string>();
			if (FilterLinq != "") lstFilter.Add(FilterLinq);
			if (FilterTemp != "") lstFilter.Add(FilterTemp);
			if (lstFilter.Count == 0) lstFilter.Add(MotoBank.MetaModel.GetDefaultFilter(EntityType));
			return string.Join(" AND ", lstFilter.ToArray());
		}
		[WebMethod]
		public object[] GetList(string EntityType,
		int FirstRow, int NumberOfRows, string FilterLinq, string FilterTemp, string OrderBy, string[] Columns, string PageType, string ControlID,
		List<Telerik.Web.UI.GridSortExpression> SortExpressions, Telerik.Web.UI.GridFilterExpression[] FilterExpressions,
		string ReportName, SerializableDictionary<string, string[]> Docks){
			bool noError = true;
			try {
				Type T = Type.GetType(EntityType, true);
				string filterLinq = BuildFilter(T, FilterLinq, FilterTemp);
				if (OrderBy + "" == "")
					OrderBy = string.Join(",", MotoBank.MetaModel.GetSortFieldsDict(T).Select(d => d.Key + (d.Value == 1 ? "" : " DESC")).ToArray());

				var columns = from column in MotoBank.MetaModel.GetClientKeyNames(T)
												.Union(Columns)
												.Union(SortExpressions.Select(se => se.FieldName))
												.Union(MotoBank.MetaModel.GetLookUpFields(T))
											join field in MotoBank.MetaModel.GetMetaTable(EntityType, true).Columns
											on column equals field.Name
											select column;
				using (var txn = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {
					IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
				})) {
					var res = new MotoBank().GetTable(T).Where(filterLinq)
						.Select("new (" + string.Join(",", columns.ToArray()) + ")")
						.OrderBy(OrderBy).Skip(FirstRow).Take(NumberOfRows);
					try {
						return res.Cast<object>().ToArray();
					} catch (Exception exc) { exc.Data["SQL"] = res.ToString(); throw exc; }
				}

			} catch (Exception exc) {
				noError = false;
				if (exc.GetType() == typeof(System.Linq.Dynamic.ParseException)) {
					throw new ASPException("Error in SQL Expression. Please reload this page.\n" + exc.Message);
				} else {
					ASPException.Publish(exc);
					while (exc.InnerException != null) exc = exc.InnerException;
					throw exc;
				}
			} finally {
				if( noError )
				lock (Profiler.ProfileLock) {
					try {
						var p = new Profiler(PageType, ControlID);
						p.Sorts_Add(ReportName, 
							SortExpressions.Where(s=>s.SortOrder!=Telerik.Web.UI.GridSortOrder.None).ToDictionary(s => s.FieldName, s => s.SortOrder.Convert()));
						p.Filters_Add(ReportName, FilterExpressions);
						if (Docks != null) p.Docks_Add(ReportName, Docks);
						p.Report(ReportName).FilterLinq = FilterLinq;
						p.Grid.PageSize = NumberOfRows;
						p.Report(ReportName).Columns = string.Join("\n", Columns);
						if (ReportName == "") p.Details_Sync();
						p.Save();
					} catch (Exception exc) {
						ASPException.Publish(exc);
						throw exc;
					}
				}
			}
		}
		[WebMethod]
		public object GetList_SetActiveRow(string PageType, string ControlID, SerializableDictionary<string,object> ActiveRowKey,string EntityType) {
			try {
				string ActiveRowKeyJson = Jayrock.Json.Conversion.JsonConvert.ExportToString(ActiveRowKey);
				lock (Profiler.ProfileLock) {
					var p = new Profiler(PageType, ControlID);
					var a = p.Grid.ActiveRowKey;
					p.Grid.ActiveRowKey = ActiveRowKeyJson;
					p.profile.Save();
				}
				if ((EntityType + "") != "")
					foreach (var ret in GetList_Details(EntityType, ActiveRowKey))
						return ret;
					return null;

			} catch (Exception exc) { throw new ClientException(exc); }
		}
		private object[] GetList_Details(string EntityType, SerializableDictionary<string, object> Keys) {
			try {
				string filter = string.Join(" && ", Keys.Select((k, i) => k.Key + "=@" + i).ToArray());
				return new MotoBank().GetTable(EntityType).Where(filter, Keys.Values.ToArray()).Cast<object>().ToArray();
			} catch (Exception exc) { ASPException.Publish(exc); throw; }
		}
		[WebMethod]
		public int GetList_Count(string EntityType, string FilterLinq, string FilterTemp) {
			try {
				Type T = Type.GetType(EntityType, true);
				string filterLinq = BuildFilter(T, FilterLinq, FilterTemp);
				return new MotoBank().GetTable(T).Where(filterLinq).Count();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public object[] GetList_Keys(string EntityType, string FilterLinq, string FilterTemp,string[] Keys) {
			try {
				Type T = Type.GetType(EntityType, true);
				string filterLinq = BuildFilter(T, FilterLinq, FilterTemp);
				return new MotoBank().GetTable(T).Where(filterLinq).Select("new (" + string.Join(",", Keys) + ")").Cast<object>().ToArray();
			} catch (Exception exc) { ASPException.Publish(exc); throw exc; }
		}
		[WebMethod]
		public void ToExcel(string EntityType, string FilterLinq, string FilterTemp, string Columns, string OrderBy) {
			var Response = Context.Response;
			Response.Clear();
			Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
			Response.Charset = "";
			Response.ContentType = "application/vnd.xls";
			System.IO.StringWriter stringWrite = new System.IO.StringWriter();
			System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
			var grv = new GridView();
			LinqDataSource linqDS = new LinqDataSource() { EnableUpdate = true };
			linqDS.ContextTypeName = typeof(MotoBank).FullName;
			var metaTable = MotoBank.MetaModel.GetMetaTable(EntityType, true);
			linqDS.TableName = metaTable.Name;
			string filterLinq = BuildFilter(metaTable.EntityType, FilterLinq, FilterTemp);
			linqDS.Where = filterLinq;
			var columns = from column in MotoBank.MetaModel.GetClientKeyNames(metaTable.EntityType)
								.Union(Columns.Split(','))
										join field in MotoBank.MetaModel.GetMetaTable(EntityType, true).Columns
										on column equals field.Name
										select column;

			linqDS.Select = "new (" + string.Join(",", columns.ToArray()) + ")";
			linqDS.OrderBy = OrderBy;
			grv.DataSource = linqDS;
			grv.DataBind();
			grv.RenderControl(htmlWrite);
			Response.Write(stringWrite.ToString());
			Response.End();
		}

		[WebMethod]
		public object[] GetList_UpdateStatus(int ID,int StatusToID) {
			try {
				string tableName = new MotoBank().v_States.Single(s=>s.ID == StatusToID).TableName;
				var u = new MotoBank().GetTable(tableName).Update("ID=@ID", new object[] { ID },
					new Dictionary<string, object>() { { "StatusID", StatusToID } });
				return new MotoBank().fs_NextStates((int)u.Cast<object>().Single().GetProperty("StatusID")).Cast<object>().ToArray();
			} catch (Exception exc) { ASPException.Publish(exc); throw; }
		}
		[WebMethod]
		public object[] Status_List(int? StatusID) {
			try {
				return (from r in new MotoBank().fs_NextStates(StatusID??0) orderby r.Dir
							 select new { v = r.StateToID, t = r.StateTo, s = r.StateToID == r.StateFromID }).Cast<object>().ToArray();
			} catch (Exception exc) { ASPException.Publish(exc); throw; }
		}
		[WebMethod]
		public object[] LookUp_List(string EntityType, object ID,string TextField,string ValueField) {
			try {
				var ret = new MotoBank().GetTable(EntityType).OrderBy(TextField);
				if (ID == null) ret = ret.Select("new (" + ValueField + " as v," + TextField + " as t,0 as s)");
				else ret = ret.Select("new (" + ValueField + " as v," + TextField + " as t," + ValueField + "==@0 as s)", ID);
				return ret.Cast<object>().ToArray();
			} catch (Exception exc) { ASPException.Publish(exc); throw; }
		}
		[WebMethod]
		public object GetList_Insert(string EntityType, SerializableDictionary<string,object> Values) {
			try {
				var inserted = new MotoBank().GetInsertTable(EntityType).Insert(Values);
				if( inserted.ElementType == MotoBank.MetaModel.GetEntityType(EntityType,true))return inserted.Cast<object>().Single();
				return new MotoBank().GetTable(EntityType).Where("ID=@0", inserted.Cast<object>().Single().GetProperty("ID"))
					.Cast<object>().Single();
			} catch (Exception exc) { ASPException.Publish(exc); throw; }
		}
		[WebMethod]
		public object[] GetList_Update(string EntityType, SerializableDictionary<string, object> Keys, string FieldName, object FieldValue) {
			try {
				string filterLinq = string.Join(" && ", Keys.Select(k => k.Key + "=" + k.Value).ToArray());
				var mb = new MotoBank();
				var updateTable = mb.GetUpdateTable(EntityType);
				if ((FieldValue + "") == "" && !MotoBank.MetaModel.GetColumn(updateTable.ElementType, FieldName).IsString) FieldValue = null;
				var ut = updateTable.Update(filterLinq, null, new Dictionary<string, object>() { { FieldName, FieldValue } });
				return mb.GetTable(EntityType).Where(filterLinq).Cast<object>().ToArray();
			} catch (NoTableException) {
				throw new Exception(EntityType + " is not updatable.");
			} catch (Exception exc) { ASPException.Publish(exc); throw; }
		}
		[WebMethod]
		public object GetList_DefaultEntity(string EntityType) {
			try {
				return Activator.CreateInstance(MotoBank.MetaModel.GetEntityType(EntityType, true));
			} catch (Exception exc) { ASPException.Publish(exc); throw; }
		}
		[WebMethod]
		public void GetList_Delete(string EntityType, SerializableDictionary<string, object> Keys) {
			var mb = new MotoBank();
			string filterLinq = string.Join(" && ", Keys.Select(k => k.Key + "=" + k.Value).ToArray());
			var t = mb.GetDeleteTable(EntityType).Where(filterLinq);
			try {
				mb.GetDeleteTable(EntityType).DeleteAllOnSubmit(t);
				mb.SubmitChanges();
			} catch (Exception exc) {
				exc.Data["FilterLinq"] = filterLinq;
				exc.Data["DeteteEntity"] = t.ElementType.Name;
				exc.Data["LinqToSQL"] = t.ToString();
				ASPException.Publish(exc); throw exc; 
			}
		}
		//public static int GetList_Count<TEntity>(string FilterLinq, string FilterTemp) where TEntity : class {
		//  try {
		//    string filterLinq = BuildFilter(FilterLinq, FilterTemp);
		//    if (FilterTemp != "") filterLinq += " AND " + FilterTemp;
		//    return new MotoBank().GetTable<TEntity>().Where(filterLinq).Count();
		//  } catch (Exception exc) { throw new ClientException(exc); }
		//}
		#endregion

		#region RafGridExtender
		[WebMethod]
		public object RadGrid_Report_Find(string PageType, string ControlID) {
			try {
				return new Profiler(PageType, ControlID).Report_FindName();
			} catch (Exception exc) { throw new ASPException(exc); }
		}
		[WebMethod]
		public void RadGrid_Report_Delete(string PageType, string ControlID, string ReportName) {
			lock (Profiler.ProfileLock) {
				new Profiler(PageType, ControlID).Report_Delete(ReportName);
			}
		}
		[WebMethod]
		public void RadGrid_Report_Copy(string PageType, string ControlID, string ReportNameFrom, string ReportNameTo) {
			new Profiler(PageType, ControlID).Report_Copy(ReportNameFrom, ReportNameTo);
		}
		[WebMethod]
		public void RadGrid_SetColumns(string[] Columns, string PageType, string ControlID, string ReportName) {
			lock (Profiler.ProfileLock) {
				var p = new Profiler(PageType, ControlID);
				p.Report(ReportName).Columns = string.Join("\n", Columns);
				p.Details_Sync();
			}
		}
		[WebMethod]
		public Dimok.Web.UI.DSs.Dimension SetBridgeDetailView(Dimok.Web.UI.DSs.Dimension Dimention, string PageType, string ControlID, string ReportName) {
			lock (Profiler.ProfileLock) {
				Profiler p = new Profiler(PageType, ControlID);
				p.Report(ReportName).DetailViewXYWH = Dimention;
				p.Details_Sync();
			}
			return Dimention;
		}
		[WebMethod]
		public void SetBridgeDetailColumns(SerializableDictionary<string, int> DetailColumns, string PageType, string ControlID, string ReportName) {
			new Profiler(PageType, ControlID).Details_Update(ReportName, DetailColumns);
		}
		#endregion

		#region Car
		[WebMethod]
		public string BookValueHistory(string VIN) {
			try{
				return new MotoBank().v_BookValueHistories.Where(r => r.VIN == VIN).ToArray()
					.Select(r => new { Value = string.Format("{0:c0}", r.Value), Description = r.Description, Date = string.Format("{0:d}", r.Date) })
					.OrderByDescending(r => r.Date)
					.ToXML("BVH").ToString();
			} catch (Exception exc) { ASPException.Publish(exc); throw exc; }
		}
		[WebMethod]
		public object GetCarPrice_Current(string EntityType, int LoanID, int BookValueTypeID) {
				var mb = new MotoBank();
				var til = mb.v_TruthInLendings.Single(s => s.LoanID == LoanID);
				try {
					if (mb.NeedPullBookValue(til.VIN, DateTime.Now).Value) {
					var ci = mb.v_CarInfos.Single(s => s.LoanID == LoanID);
					var cp = Lib.GetCarPrice(til.VIN, ci.MileageByAgeCurrent.Value, til.LoanState,ci.Description, BookValueTypeID);
					if (cp.Price >= 0)
						mb.GetUpdateTable(EntityType).Update("LoanID=@0", new object[] { LoanID },
							new Dictionary<string, object>() { { "BookValueCurrent", cp.Price }, { "BookValueCurrentTypeID", cp.TypeID } });
				}
				foreach (var ret in new MotoBank().GetTable(EntityType.Split('.').Last()).Where("LoanID = @0", LoanID))
					return ret;
				throw new Exception("There is no Loan with ID:" + LoanID);
			} catch (Exception exc) {
					exc.Data["LoanID"] = LoanID;
					exc.Data["VIN"] = til.VIN;
				ASPException.Publish(exc); throw exc; 
			}
		}

		public object[] GetCarBookPriceTypes(int CarBookProviderID) {
			try {
				return new MotoBank().ts_CarBookProvider_ValueTypes.Where(r => r.ProviderID == CarBookProviderID)
					.Select(r => new { t = r.TypeName, v = r.ID }).ToArray().Union(new[] { new { t = "", v = -1 } }).OrderBy(r => r.v)
					.Cast<object>().ToArray();
			} catch (Exception exc) { ASPException.Publish(exc); throw exc; }
		}
		[WebMethod]
		public void GetCredit(int LoanID, int BureauID) {
			try {
				var uox = new Dimok.Aspx.Manager.UOX();
				uox.applID = LoanID;
				uox.PullCredit(0, (CreditBureau)BureauID);
			} catch (Exception exc) { ASPException.Publish(exc); throw; }
		}
		#endregion

		#region Images
		[WebMethod]
		public object[] GetImagePages(int LoanID) {
			try {
				return new MotoBank().PageCounts(LoanID).Where(p=>p.Count.HasValue).OrderBy(p=>p.TabIndex).ToArray();
			} catch (Exception exc) { ASPException.Publish(exc); throw; }
		}
		#endregion

		#region Attachments
		[WebMethod]
		public void Document_Delete(int ID) {
			try {
				var mb = new MotoBank();
				mb.t_Documents.DeleteAllOnSubmit(mb.t_Documents.Where(d => d.ID == ID));
				mb.SubmitChanges();
			} catch (Exception exc) { ASPException.Publish(exc); throw exc; }
		}
		[WebMethod]
		public void ShowDocument(int ID, bool DoSave) {
			var Response = Context.Response;
			try {
				var doc = new MotoBank().t_Documents.Where(d => d.ID == ID).Single();
				Response.Clear();
				if (DoSave) Response.AddHeader("content-disposition", "attachment; filename=\"" + Regex.Replace(doc.Name,"[^\\w.]","_") + "\"");
				Response.ContentType = doc.ContentType;
				Response.BinaryWrite(doc.Body.ToArray());
			} catch (Exception exc) {
				ASPException.Publish(exc);
				Response.ContentType = "text/html";
				Response.Write("<pre>" + ClientException.GetExceptions(exc) + "</pre>");
			}
			Response.End();
		}
		[WebMethod]
		public object GetDocumentsTable(string EntityType, SerializableDictionary<string, object> Keys) {
			try {
				string GUID = GetList_GuidDocs(EntityType, Keys);
				var fields = new[] {
					new BoundField(){
						DataField="Icon", HtmlEncode=false,HeaderText=" ",
						DataFormatString="<img action='show' style='vertical-align:middle' src='/images/extentions/{0}'> "},
					new BoundField() { DataField = "Name",DataFormatString="<span action='show'>{0}</span>",HtmlEncode=false },
					new BoundField() { DataField = "Date" } ,
					new BoundField() { DataField = "ID", HtmlEncode=false,DataFormatString="<img action='delete' src='/App_Themes/Office2007/Grid/Delete.gif'>",HeaderText="<img src='/App_Themes/Office2007/Grid/Delete.gif'>" } 
				};
				//string columns = "<span legend>{" + string.Join(",", fields.Select((f, i) => "'" + f.DataField + "':" + i).ToArray()) + "}</span>";
				string html = RenderTable<MotoBank>.Render("docs", typeof(v_Document), fields, null, new { GUID_Table = new Guid(GUID) });
				var columns = fields.Select((f, i) => new { Name = f.DataField, Index = i }).ToDictionary(f => f.Name, f => f.Index);
				return new { html = html, legend = columns };
			} catch (Exception exc) { ASPException.Publish(exc); throw exc; }
		}
		[WebMethod]
		public string GetList_GuidDocs(string EntityType, SerializableDictionary<string, object> Keys) {
			try {
				string filter = string.Join(" && ", Keys.Select((k, i) => k.Key + "=@" + i).ToArray());
				return new MotoBank().GetTable(EntityType).Where(filter, Keys.Values.ToArray()).Select("GUID_Documents").Cast<object>().Single() + "";
			} catch (Exception exc) { ASPException.Publish(exc); throw exc; }
		}
		[WebMethod]
		public object[] GetDocuments(string GUID) {
			try {
				return new MotoBank().v_Documents.Where(d => d.GUID_Table == new Guid(GUID)).ToArray();
			} catch (Exception exc) { ASPException.Publish(exc); throw exc; }
		}
		#endregion

		#region Decision
		[WebMethod]
		public object[] GetDecisionPrograms(int BankID) {
			try{
				return new MotoBank().v_DecisionPrograms.Where(dp => dp.BankID == BankID).Cast<object>().ToArray();
			} catch (Exception exc) { ASPException.Publish(exc); throw; }
		}
		#endregion
	}
}